/*****************************************************
 * HISTORY
 * @version 1.0 2010-5-6 20:39 @author 王乃飞
 * *** excel读取类
 * @see 需要apache poi包
 *****************************************************/
package com.senyint.sys.utils.office;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.senyint.sys.utils.DateUtil;
import com.senyint.sys.utils.POJOUtil;
import com.senyint.sys.utils.StringUtils;
import com.senyint.sys.utils.log.Log;

public class ExcelUtil {
	public static final String RETURN_LIST = "list";

	public static List<Map<String, Object>> readExcelFile(String xlsurl, int readBeginRow, int readBeginColumn, int readEndColumn) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			File file = new File(xlsurl);
			if ((file.exists()) && (file.isFile())) {
				FileInputStream in = new FileInputStream(file);

				list = readExcelFile(in, readBeginRow, readBeginColumn, readEndColumn);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return list;
	}

	public static List<Map<String, Object>> readExcelFile(InputStream in, int readBeginRow, int readBeginColumn, int readEndColumn) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Workbook wb = null;
		try {
			wb = new HSSFWorkbook(in);
			int sheetsLen = wb.getNumberOfSheets();
			Log.INSTANCE.debug("共有sheet页：{}", sheetsLen);
			List<List<List<String>>> wbList = new ArrayList<List<List<String>>>();
			for (int i = 0; i < sheetsLen; i++) {
				Sheet sheet = wb.getSheetAt(i);
				String sheetName = wb.getSheetName(i);
				Log.INSTANCE.debug("解析sheet页：{}", sheetName);
				List<List<String>> sheetList = new ArrayList<List<String>>();
				Row row0 = sheet.getRow(0);
				if ((readEndColumn < 0) && (row0 != null)) {
					int cj = 0;
					for (int ck = 1; cj < ck; cj++) {
						String cellstr = getCellValue(row0.getCell(cj));
						if (StringUtils.isEmpty(cellstr)) {
							readEndColumn = cj;
							break;
						}
						ck++;
					}
				}

				int j = readBeginRow;
				for (int k = readBeginRow + 1; j < k; j++) {
					Row row = sheet.getRow(j);
					if (row != null) {
						boolean hasValue = false;
						List<String> rowlist = new ArrayList<String>();
						int ci = readBeginColumn;
						for (int cl = readEndColumn; ci <= cl; ci++) {
							Cell cell = row.getCell(ci);
							String cellValue = getCellValue(cell);
							if ((!hasValue) && (StringUtils.isNotEmpty(cellValue))) {
								hasValue = true;
							}
							rowlist.add(cellValue);
						}
						if (hasValue) {
							k++;
							sheetList.add(rowlist);
						}
					}
				}
				wbList.add(sheetList);
			}
		} catch (IOException e) {
			e.printStackTrace();
			try {
				in.close();
			} catch (IOException e1) {
				e.printStackTrace();
			}
		} finally {
			try {
				if (wb != null) {
					wb.close();
				}
				in.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	private static String getCellValue(Cell cell) {
		String value = "";
		if (cell != null) {
			int type = cell.getCellType();
			switch (type) {
			case 0:
				NumberFormat nf = NumberFormat.getInstance();
				nf.setGroupingUsed(false);
				double ncv = cell.getNumericCellValue();
				value = nf.format(ncv);
				break;
			case 1:
				value = cell.getStringCellValue();
				break;
			default:
				Log.INSTANCE.debug("未读取列[{}]列类型[{}]", cell.getColumnIndex(), type);
			}
		}
		return value;
	}

	public static void main(String[] args) {
		String xlsurl = "D:\\工作\\2000W\\1-200W.csv";
		readExcelFile(xlsurl, 1, 0, 31);
	}

	@SuppressWarnings("unchecked")
	private static void createWorkBook(Object obj, HSSFRow row, boolean stringvalue, List<String> keys) {
		if ((obj instanceof Map))
			createWorkBook((Map<String, Object>) obj, row, stringvalue, keys);
		else
			try {
				createWorkBook(POJOUtil.PojoToMap(obj), row, stringvalue, keys);
			} catch (Exception e) {
				e.printStackTrace();
			}
	}

	private static void createWorkBook(Map<String, Object> map, HSSFRow row, boolean stringvalue, List<String> keys) {
		if (keys == null) {
			keys = new ArrayList<String>();
			keys.addAll(map.keySet());
		}
		for (int i = 0, l = keys.size(); i < l; i++) {
			HSSFCell cell = row.createCell(i);
			Object value = null;
			String[] split = keys.get(i).split(",");
			if (split.length == 1) {
				value = map.get(split[0]);
			} else {
				ArrayList<Object> list = new ArrayList<Object>();
				for (String k : split) {
					list.add(map.get(k));
				}
				value = StringUtils.join(list, "");
			}

			if (stringvalue) {
				String v = "";
				if ((value instanceof Integer))
					v = String.valueOf(value);
				else if ((value instanceof Date))
					v = DateUtil.dateToStr((Date) value);
				else if ((value instanceof Boolean))
					v = String.valueOf(value);
				else if ((value instanceof Double))
					v = String.valueOf(value);
				else if ((value instanceof String))
					v = String.valueOf(value);
				else if ((value instanceof BigDecimal))
					v = ((BigDecimal) value).toString();
				cell.setCellValue(v);
			} else {
				if ((value instanceof Integer))
					cell.setCellValue(((Integer) value).intValue());
				else if ((value instanceof Date))
					cell.setCellValue((Date) value);
				else if ((value instanceof Boolean))
					cell.setCellValue(value.toString());
				else if ((value instanceof Double))
					cell.setCellValue(((Double) value).doubleValue());
				else if ((value instanceof String))
					cell.setCellValue((String) value);
			}
		}
	}

	public static HSSFWorkbook buildExcel(String sheetname, List<? extends Object> list, boolean stringvalue, List<String> keys) {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(sheetname);
		for (int i = 0, l = list.size(); i < l; i++) {
			HSSFRow row = sheet.createRow(i);
			createWorkBook(list.get(i), row, stringvalue, keys);
		}
		return wb;
	}

	public static HSSFWorkbook buildExcel(List<? extends Object> list) {
		return buildExcel("sheet1", list, false, null);
	}

	public static void writeExcelFile(HSSFWorkbook wb, File file) {
		try {
			FileOutputStream outputStream = new FileOutputStream(file);
			wb.write(outputStream);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
